﻿using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using System.Text;
using SlamCms.Common;

namespace SlamCms.Data
{
	internal class SqlQueryGenerator
	{
		private StringBuilder _tempTablesBuilder;

		private StringBuilder _firstStepSelectBuilder;
		private StringBuilder _firstStepFromBuilder;
		private StringBuilder _firstStepWhereBuilder;
		private StringBuilder _firstStepOrderBuilder;

		private TimeSpan _queryBuildElapsedTime;
		private readonly QueryParameters _parameters;

		public SqlQueryGenerator(QueryParameters parameters)
		{
			_parameters = parameters;
		}

		public static string Generate(QueryParameters parameters)
		{
			return new SqlQueryGenerator(parameters).Generate();
		}

		public TimeSpan QueryBuildElapsedTime
		{
			get
			{
				return _queryBuildElapsedTime;
			}
		}

		public string Generate()
		{
			var stopWatch = Stopwatch.StartNew();

			// temp tables
			_tempTablesBuilder = new StringBuilder();
			AddTempTableForTaggedWithAll();
			AddTempTableForTaggedWithAny();
            AddTempTableForNotTaggedWithAll();
			AddRelatedToContentItem();

			var firstStep = FirtStep();

			// render
			var sql = _tempTablesBuilder.ToString() +
					   Environment.NewLine +
					   firstStep;

			stopWatch.Stop();

			_queryBuildElapsedTime = stopWatch.Elapsed;

			return sql;
		}

		protected string FirtStep()
		{
			// select
			_firstStepSelectBuilder = new StringBuilder();
			_firstStepSelectBuilder.Append(@"select ");

			if (_parameters.CountOnly)
			{
				_firstStepSelectBuilder.AppendLine(@" count(1)");
			}
			else
			{
				AddTake();
				AddSelectFields();
				AddSelectFeatureLevelForOrdering();
				AddSelectForN1();
			}

			// from
			_firstStepFromBuilder = new StringBuilder();
			_firstStepFromBuilder.AppendLine(@"from ContentItem with (nolock)");

			if (_parameters.Sites.Any() || _parameters.Zones.Any())
				_firstStepFromBuilder.AppendLine(@"inner join [SLAM].[List] List with (nolock) on List.ListID = ContentItem.ListID");

			AddJoinForTaggedWithAll();
			AddJoinForTaggedWithAny();
            AddJoinForNotTaggedWithAll();

			if (!_parameters.RelatedToContentItemId.IsNullOrEmpty())
				_firstStepFromBuilder.AppendLine(@"inner join #RelatedContentItems rci with (nolock) on rci.ContentItemId = ContentItem.ContentItemId");

			AddContentItemChildTables();

			if (_parameters.IncludeTags)
				_firstStepFromBuilder.AppendLine(@"left join ContentItemTag with (nolock) on ContentItemTag.ContentItemId = ContentItem.ContentItemId");

			// where
			_firstStepWhereBuilder = new StringBuilder();
			FilterStatus();
			FilterLists();
			FilterSites();
			FilterZones();
			FilterContentTypes();
			FilterFeaturedOnTag();
			FilterFeaturedOnAnyTag();
			FilterFeaturedOnLocation();
			FilterAdditional();
			FilterContentItemsIds();

			// order by
			_firstStepOrderBuilder = new StringBuilder();
			AddOrder();

			return _firstStepSelectBuilder.ToString().TrimEnd().TrimEnd(',') + Environment.NewLine +
					   _firstStepFromBuilder.ToString() +
					   _firstStepWhereBuilder.ToString() + Environment.NewLine +
					   _firstStepOrderBuilder.ToString();
		}

		protected void AddTake()
		{
			if (_parameters.Take > 0 && !_parameters.IncludeTags)
				_firstStepSelectBuilder.AppendLine(@"top {0}".F(_parameters.Take));
		}

		protected void AddSelectFields()
		{
			// TODO cache this, it's everytime the same

			var sb = _firstStepSelectBuilder;

			AddSelectContentTypeDiscriminator(sb);
			AddSelectFields(sb, typeof(ContentItem));

			var filteredContentTypes = GetFilteredContentTypes();

			foreach (var contentType in _parameters.KnownContentTypes)
			{
				// use null values if the content type is not selected 
				var nullValues = !contentType.In(filteredContentTypes);
				AddSelectFields(sb, contentType, nullValues);
			}
		}

		protected IList<Type> GetFilteredContentTypes()
		{
			var contentTypes = _parameters.KnownContentTypes.ToList();

			if (_parameters.ContentTypes.Any())
				contentTypes = _parameters.ContentTypes.ToList();

			return contentTypes;
		}

		protected void AddSelectContentTypeDiscriminator(StringBuilder selectBuilder)
		{
			if (!_parameters.KnownContentTypes.Any())
			{
				selectBuilder.AppendLine(@"'' {0},".F(Constants.ContentTypeDiscriminatorFieldName));
				return;
			}

			selectBuilder.Append(@"case ");

			foreach (var contentType in GetFilteredContentTypes())
				selectBuilder.AppendLine(@"when {0}.ContentItemId is not null then '{0}' ".F(contentType.Name));

			selectBuilder.AppendLine(@"end {0},".F(Constants.ContentTypeDiscriminatorFieldName));
		}

		protected void AddSelectFields(StringBuilder selectBuilder, Type type, bool nullValues = false)
		{
			var tableName = type.Name;
			foreach (var propertyName in ContentTypeCollection.GetProperties(type, "FeatureLevel"))
			{
				if (nullValues)
					selectBuilder.AppendLine(@"null '{0}.{1}',".F(tableName, propertyName));
				else
					selectBuilder.AppendLine(@"[{0}].[{1}] '{0}.{1}',".F(tableName, propertyName));
			}
		}

	    protected void AddTempTableForNotTaggedWithAll()
	    {
            if (!_parameters.NotTaggedWithAll.Any())
                return;

            var serializedTags = _parameters.NotTaggedWithAll.ToSqlIn();

            var sql = @"if OBJECT_ID('tempdb..#ContentItemsNotTaggedWithAll') is not null
                            drop table #ContentItemsNotTaggedWithAll
						select cit.ContentItemId, count(cit.TagID) SharedTags
						into #ContentItemsNotTaggedWithAll					
						from ContentItemTag cit with (nolock)
						inner join Tag t with (nolock) on t.TagID = cit.TagID
						where t.Identifier in ('us')
						group by cit.ContentItemID
						except (
							select cit.ContentItemId, count(cit.TagId) from ContentItemTag cit
							join Tag t on cit.TagId = t.TagId and t.Identifier in ({0})
							group by cit.ContentItemId)".F(serializedTags);

            this._tempTablesBuilder.AppendLine(sql);
	    }

		protected void AddTempTableForTaggedWithAll()
		{
			if (!_parameters.TaggedWithAll.Any())
				return;

			var serializedTags = _parameters.TaggedWithAll.ToSqlIn();

			var sql = @"if OBJECT_ID('tempdb..#ContentItemsTaggedWithAll') is not null
							drop table #ContentItemsTaggedWithAll

						select cit.ContentItemId, count(cit.TagID) SharedTags
						into #ContentItemsTaggedWithAll
						from ContentItemTag cit with (nolock)
						inner join Tag t with (nolock) on t.TagID = cit.TagID
						where t.Identifier in ({0})
						group by cit.ContentItemID
						having COUNT(1) >= {1}".F(serializedTags, _parameters.TaggedWithAll.Count);

			this._tempTablesBuilder.AppendLine(sql);
		}

		protected void AddJoinForTaggedWithAll()
		{
			if (!_parameters.TaggedWithAll.Any())
				return;

			_firstStepFromBuilder.AppendLine(@"inner join #ContentItemsTaggedWithAll ContentItemsTaggedWithAll on ContentItem.ContentItemId = ContentItemsTaggedWithAll.ContentItemId");
		}

	    protected void AddJoinForNotTaggedWithAll()
	    {
            if (!_parameters.NotTaggedWithAll.Any())
                return;

            _firstStepFromBuilder.AppendLine(@"inner join #ContentItemsNotTaggedWithAll ContentItemsNotTaggedWithAll on ContentItem.ContentItemId = ContentItemsNotTaggedWithAll.ContentItemId");
	    }

		protected void AddTempTableForTaggedWithAny()
		{
			if (!_parameters.TaggedWithAny.Any())
				return;

			var serializedTags = _parameters.TaggedWithAny.ToSqlIn();

			var sql = @"if OBJECT_ID('tempdb..#ContentItemsTaggedWithAny') is not null
							drop table #ContentItemsTaggedWithAny

						select cit.ContentItemId, count(cit.TagID) SharedTags
						into #ContentItemsTaggedWithAny
						from ContentItemTag cit with (nolock)
						inner join Tag t with (nolock) on t.TagID = cit.TagID
						where t.Identifier in ({0})
						group by cit.ContentItemID".F(serializedTags);

			_tempTablesBuilder.AppendLine(sql);
		}

		protected void AddJoinForTaggedWithAny()
		{
			if (!_parameters.TaggedWithAny.Any())
				return;

			_firstStepFromBuilder.AppendLine(@"inner join #ContentItemsTaggedWithAny ContentItemsTaggedWithAny on ContentItem.ContentItemId = ContentItemsTaggedWithAny.ContentItemId");
		}

		protected void AddSelectFeatureLevelForOrdering()
		{
			if (!_parameters.OrderByFeatureOnTagLevel.IsNullOrEmpty())
			{
				_firstStepSelectBuilder.AppendLine(@"isnull((select FeatureLevel 
													from ContentItemFeatureOnTag cift with (nolock)
													inner join Tag t with (nolock) on t.TagID = cift.TagID
													where cift.ContentItemId = ContentItem.ContentItemId 
													and t.Identifier = '{0}'), -10) FeatureLevel,".F(this._parameters.OrderByFeatureOnTagLevel));
			}
			else if (!_parameters.OrderByFeatureOnLocationLevel.IsNullOrEmpty())
			{
				_firstStepSelectBuilder.AppendLine(@"isnull((select FeatureLevel
													from ContentItemFeatureLocation cifl with (nolock)
													where cifl.ContentItemId = ContentItem.ContentItemId
													and cifl.FeatureLocationId = '{0}'), -10) FeatureLevel,".F(this._parameters.OrderByFeatureOnLocationLevel));
			}
			else
			{
				_firstStepSelectBuilder.AppendLine(@"-10 FeatureLevel,");
			}
		}

		private void AddSelectForN1()
		{
			if (_parameters.IncludeTags)
				_firstStepSelectBuilder.AppendLine(@"ContentItemTag.TagId TagId, ");
			else
				_firstStepSelectBuilder.AppendLine(@"null TagId,");
		}

		protected void AddSelect()
		{
			if (_parameters.RelatedToContentItemId.IsNullOrEmpty())
				_firstStepSelectBuilder.AppendLine(@"0 SharedTags,");
			else
				_firstStepSelectBuilder.AppendLine(@"rci.SharedTags,");
		}

		protected void AddContentItemChildTables()
		{
			var contentTypes = _parameters.KnownContentTypes;
			if (_parameters.ContentTypes.Any())
				contentTypes = _parameters.ContentTypes;

			foreach (var contentType in contentTypes)
			{
				var tableName = contentType.Name;
				_firstStepFromBuilder.AppendLine(@"left join {0} with (nolock) on {0}.ContentItemId = ContentItem.ContentItemId".F(tableName));
			}
		}

		protected void AddOrder()
		{
			if (_parameters.CountOnly)
				return;

			if (!_parameters.OrderBy.IsNullOrEmpty())
				_firstStepOrderBuilder.AppendLine(@"order by {0}".F(_parameters.OrderBy));
			else if (!_parameters.RelatedToContentItemId.IsNullOrEmpty())
				_firstStepOrderBuilder.AppendLine(@"order by rci.SharedTags desc, 'ContentItem.ModifiedDate' desc");
			else if (!_parameters.OrderByFeatureOnTagLevel.IsNullOrEmpty() || !_parameters.OrderByFeatureOnLocationLevel.IsNullOrEmpty())
				_firstStepOrderBuilder.AppendLine(@"order by FeatureLevel desc, 'ContentItem.ModifiedDate' desc");
			else
				_firstStepOrderBuilder.AppendLine(@"order by 'ContentItem.ModifiedDate' desc");
		}

		protected void FilterStatus()
		{
			if (_parameters.Status == Data.FilterStatus.None)
			{
				_firstStepWhereBuilder.AppendLine(@"where 1 = 1");
			}
			else if (_parameters.Status == Data.FilterStatus.Draft)
			{
				var serializedStatuses = (new[] { ContentItemStatus.New, ContentItemStatus.PendingApproval, ContentItemStatus.Modified, ContentItemStatus.ModifiedPendingApproval }).ToSqlIn();
				_firstStepWhereBuilder.AppendLine(@"where ContentItem.StatusID in ({0})".F(serializedStatuses));
			}
			else if (_parameters.Status == Data.FilterStatus.LiveAndNotExpired)
			{
				_firstStepWhereBuilder.AppendLine(@"where ContentItem.StatusID = {0} and (ContentItem.ExpirationDate is null or ContentItem.ExpirationDate > GETDATE())".F(ContentItemStatus.Live));
			}
			else if (_parameters.Status == Data.FilterStatus.Live)
			{
				_firstStepWhereBuilder.AppendLine(@"where ContentItem.StatusID = {0}".F(ContentItemStatus.Live));
			}
			else if (_parameters.Status == Data.FilterStatus.PreviewMode)
			{
				_firstStepWhereBuilder.AppendLine(@"where (ContentItem.StatusID not in ({0},{1}) or not exists (select 1 from ContentItem c1 with (nolock) where c1.PrimaryContentItemId = ContentItem.ContentItemId and c1.StatusID != {0}))".F(ContentItemStatus.Live, ContentItemStatus.Retired));
			}
			else if (_parameters.Status == Data.FilterStatus.LiveOrRetired)
			{
				var serializedStatuses = (new[] { ContentItemStatus.Live, ContentItemStatus.Retired }).ToSqlIn();
				_firstStepWhereBuilder.AppendLine(@"where ContentItem.StatusID in ({0})".F(serializedStatuses));
			}
		}

		protected void FilterLists()
		{
			if (!_parameters.Lists.Any())
				return;

			var listIds = _parameters.Lists.ToSqlIn();

			_firstStepWhereBuilder.AppendLine(@"and ContentItem.ListID in ({0})".F(listIds));
		}

		protected void FilterSites()
		{
			if (!_parameters.Sites.Any())
				return;

			var siteIds = _parameters.Sites.ToSqlIn();

			_firstStepWhereBuilder.AppendLine(@"and List.SiteID in ({0})".F(siteIds));
		}

		protected void FilterZones()
		{
			if (!_parameters.Zones.Any())
				return;

			var zoneIds = _parameters.Zones.ToSqlIn();

			_firstStepWhereBuilder.AppendLine(@"and exists (select 1 from ZoneSite where SiteID = List.SiteID and ZoneID in ({0}))".F(zoneIds));
		}

		protected void FilterContentTypes()
		{
			if (!_parameters.ContentTypes.Any())
				return;

			var items = _parameters.ContentTypes.Select(i => i.Name + @".ContentItemId");
			var serializedContentTypes = items.ToSqlIn(false);
			_firstStepWhereBuilder.AppendLine(@"and coalesce({0}, '') <> ''".F(serializedContentTypes));
		}

		protected void FilterFeaturedOnTag()
		{
			if (_parameters.FeaturedOnAllTags.Count == 0)
				return;

			foreach (var featuredOnTag in _parameters.FeaturedOnAllTags)
			{
				_firstStepWhereBuilder.AppendLine(@"and exists (select 1 
													from ContentItemFeatureOnTag cift with (nolock) 
													inner join Tag t with (nolock) on t.TagID = cift.TagID
													where cift.ContentItemId = ContentItem.ContentItemId 
													and t.Identifier = '{0}' and cift.FeatureLevel {1} {2})".F(featuredOnTag.TagIdentifier, featuredOnTag.FilterOperator.ToString(), featuredOnTag.FeatureLevel));
			}
		}

		protected void FilterFeaturedOnAnyTag()
		{
			if (_parameters.FeaturedOnAnyTags.Count == 0)
				return;

			_firstStepWhereBuilder.AppendLine(@"and (");

			foreach (var featuredOnTag in _parameters.FeaturedOnAnyTags)
			{
				_firstStepWhereBuilder.AppendLine(@"exists (select 1 
													from ContentItemFeatureOnTag cift with (nolock) 
													inner join Tag t with (nolock) on t.TagID = cift.TagID
													where cift.ContentItemId = ContentItem.ContentItemId 
													and t.Identifier = '{0}' and cift.FeatureLevel {1} {2})".F(featuredOnTag.TagIdentifier, featuredOnTag.FilterOperator.ToString(), featuredOnTag.FeatureLevel));

				if (featuredOnTag != _parameters.FeaturedOnAnyTags.LastOrDefault())
					_firstStepWhereBuilder.AppendLine(@"or");
			}

			_firstStepWhereBuilder.AppendLine(@")");
		}

		protected void FilterFeaturedOnLocation()
		{
			var featuredOnLocation = _parameters.FeaturedOnLocation;

			if (featuredOnLocation == null)
				return;

			_firstStepWhereBuilder.AppendLine(@"and exists (select 1 
													from ContentItemFeatureLocation cifl with (nolock) 
													where cifl.ContentItemId = ContentItem.ContentItemId 
													and cifl.FeatureLocationId = '{0}' and cifl.FeatureLevel {1} {2})".F(featuredOnLocation.Location, featuredOnLocation.FilterOperator.ToString(), featuredOnLocation.FeatureLevel));
		}

		protected void FilterAdditional()
		{
			foreach (var additionalFilter in _parameters.AdditionalFilters)
				_firstStepWhereBuilder.AppendLine(@"and {0}".F(additionalFilter));
		}

		protected void FilterContentItemsIds()
		{
			if (_parameters.ContentItemIds.Count == 0)
				return;

			var serializedContentItemIds = _parameters.ContentItemIds.ToSqlIn();
			_firstStepWhereBuilder.AppendLine(@"and ContentItem.PrimaryContentItemId in ({0})".F(serializedContentItemIds));
		}

		//protected void FilterPage()
		//{
		//    var pageFilter = this.filters.PageFilter;

		//    if (pageFilter == null)
		//        return;

		//    if (this.filters.Take > 0)
		//        throw new Exception("Cannot use the Take method and use the page filtering at the same time.");

		//    var rowNumberFrom = (pageFilter.Number - 1) * pageFilter.PageSize + 1;
		//    var rowNumberTo = pageFilter.Number * pageFilter.PageSize;

		//    this.secondStepWhereBuilder.AppendLine(@"and RowNumber between {0} and {1}".F(rowNumberFrom, rowNumberTo));
		//}

		protected void AddTempTable(string sql)
		{
			_tempTablesBuilder.Append(sql);
		}

		protected void AddTempTable<T>(StringBuilder sb, string name, T[] values)
		{
			sb.AppendLine(@"create table #{0} ( Value {1} not null )".F(name, ""));
			foreach (var value in values)
			{
				sb.AppendLine(@"insert into #{0} values ({1})".F(name, value));
			}
		}

		protected void AddRelatedToContentItem()
		{
			if (_parameters.RelatedToContentItemId.IsNullOrEmpty())
				return;

			var sql = @"if OBJECT_ID('tempdb..#RelatedContentItems') is not null
							drop table #RelatedContentItems

						(select cit.ContentItemId, count(cit.TagID) SharedTags
						into #RelatedContentItems
						from ContentItemTag cit with (nolock)
						inner join ContentItemTag citr with (nolock) on citr.TagID = cit.TagID and citr.ContentItemID = '{0}' 
						inner join (select distinct TagId
									from TagHierarchyPosition thp with (nolock) 
									inner join TagCategory tc with (nolock) on tc.TagCategoryId = thp.TagCategoryId
									where tc.IsRelatable = 1) X on X.TagID = citr.TagId
						where cit.ContentItemID != '{0}'
						group by cit.ContentItemID)".F(_parameters.RelatedToContentItemId);

			_tempTablesBuilder.Append(sql);
		}
	}
}